Imports System.Data
Imports System.Data.SqlClient

Public Class ClearPlanCache

    Private _connectionString As String = ""
    Private _systemTables As New ArrayList

    Public Sub New(ByVal ConnectionString As String)

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        _connectionString = ConnectionString

        _systemTables.Add("distribution")
        _systemTables.Add("master")
        _systemTables.Add("model")
        _systemTables.Add("msdb")
        _systemTables.Add("tempdb")

    End Sub

    Private Sub ClearPlanCache_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim Conn As New SqlConnection(_connectionString)

        Try
            Conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
            Conn.Close()
            Conn.Dispose()
            Conn = Nothing
            Me.Close()
            Exit Sub
        End Try

        Dim Sql As String = "SELECT name, dbid FROM master.dbo.sysdatabases"
        Dim Command As New SqlCommand(Sql, Conn)
        Dim Dr As SqlDataReader

        Try
            Dr = Command.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message)
            Dr = Nothing
            Command.Dispose()
            Command = Nothing
            Conn.Close()
            Conn.Dispose()
            Conn = Nothing
            Exit Sub
        End Try

        If Dr.HasRows = True Then
            While Dr.Read
                Dim DbEntry As New DbInfo
                DbEntry.DatabaseName = Dr("name").ToString
                DbEntry.DbId = Dr("dbid").ToString
                CheckedListBox1.Items.Add(DbEntry)
            End While
        End If

        Dr.Close()
        Dr = Nothing
        Command.Dispose()
        Command = Nothing
        Conn.Close()
        Conn.Dispose()
        Conn = Nothing

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Warning As Boolean = False

        For Each Item As DbInfo In CheckedListBox1.CheckedItems
            If _systemTables.Contains(Item.DatabaseName) Then
                Warning = True
            End If
        Next

        If Warning = True Then    
            Dim WarningMessage As System.Windows.Forms.DialogResult = MessageBox.Show("WARNING:  You have selected one or more system databases (distribution, master, model, msdb or tempdb).  This may cause unexpected results if you continue.  Proceed at your own risk.", "Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)

            If WarningMessage = Windows.Forms.DialogResult.Cancel Then
                lblStatus.Text = "Status:  Action Cancelled"
                Exit Sub
            End If

        End If

        Dim Answer As Integer = MsgBox("Are you sure you want to clear the plan cache of the selected databases?", MsgBoxStyle.YesNoCancel, "Clear Cache")

        If Answer = vbNo Or Answer = vbCancel Then
            lblStatus.Text = "Status:  Action Cancelled"
            Exit Sub
        End If

        Dim Conn As New SqlConnection(_connectionString)

        Try
            Conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
            Conn.Close()
            Conn.Dispose()
            Conn = Nothing
            Me.Close()
            Exit Sub
        End Try

        Dim Command As New SqlCommand
        Command.Connection = Conn

        If chkClearAll.Checked = True Then
            Command.CommandText = "DBCC FREEPROCCACHE"

            Try
                Command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        Else
            For Each Item As DbInfo In CheckedListBox1.CheckedItems
                lblStatus.Text = "Clearing " & Item.DatabaseName & " (DbId=" & Item.DbId & ")"
                Application.DoEvents()

                Command.CommandText = "DBCC FLUSHPROCINDB (" & Item.DbId & ")"

                Try
                    Command.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

            Next
        End If

        MsgBox("Operation Complete")

        Command.Dispose()
        Command = Nothing
        Conn.Close()
        Conn.Dispose()
        Conn = Nothing

        Me.Close()

    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkClearAll.CheckedChanged
        If chkClearAll.Checked = True Then
            CheckedListBox1.Enabled = False
        Else
            CheckedListBox1.Enabled = True
        End If
    End Sub

End Class